package com.gymmsystem.GYMMSystem.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.gymmsystem.GYMMSystem.entity.Reservation;
import com.gymmsystem.GYMMSystem.dto.UserReservationDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import java.util.List;
import java.util.Map;

@Mapper
public interface ReservationMapper extends BaseMapper<Reservation> {

    @Select("SELECT r.*, c.c_name, c.c_time, co.name as coach_name " +
            "FROM reservations r " +
            "LEFT JOIN courses c ON r.r_c_id = c.c_id " +
            "LEFT JOIN coaches co ON r.r_t_id = co.id " +
            "WHERE r.r_u_id = #{userId} " +
            "ORDER BY r.r_created_at DESC")
    List<Map<String, Object>> findReservationsByUserId(@Param("userId") Long userId);
    
    /**
     * 查询用户预约记录并返回DTO格式
     * @param userId 用户ID
     * @return 用户预约DTO列表
     */
    @Select("SELECT r.r_id as id, c.c_name as courseName, c.c_time as courseStartTime, " +
            "DATE_ADD(c.c_time, INTERVAL c.c_duration MINUTE) as courseEndTime, " +
            "r.r_notes as notes, r.r_created_at as createdAt, r.r_updated_at as updatedAt, " +
            "co.name as coachName, co.phone as coachPhone, r.r_status as status " +
            "FROM reservations r " +
            "LEFT JOIN courses c ON r.r_c_id = c.c_id " +
            "LEFT JOIN coaches co ON r.r_t_id = co.id " +
            "WHERE r.r_u_id = #{userId} " +
            "ORDER BY r.r_created_at DESC")
    List<UserReservationDTO> findUserReservationsWithDTO(@Param("userId") Long userId);

    @Select("SELECT r.*, u.u_nickname as user_name, c.c_name, c.c_time " +
            "FROM reservations r " +
            "LEFT JOIN users u ON r.r_u_id = u.user_id " +
            "LEFT JOIN courses c ON r.r_c_id = c.c_id " +
            "WHERE r.r_t_id = #{coachId} " +
            "ORDER BY r.r_created_at DESC")
    List<Map<String, Object>> findReservationsByCoachId(@Param("coachId") Long coachId);

    @Select("SELECT COUNT(*) FROM reservations WHERE r_u_id = #{userId} AND r_c_id = #{courseId} AND r_status != '已取消'")
    int checkUserCourseReservation(@Param("userId") Long userId, @Param("courseId") Long courseId);
    
    /**
     * 查询用户对特定课程的预约记录（包括所有状态）
     */
    @Select("SELECT * FROM reservations WHERE r_u_id = #{userId} AND r_c_id = #{courseId}")
    Reservation findReservationByUserAndCourse(@Param("userId") Long userId, @Param("courseId") Long courseId);

    @Update("UPDATE reservations SET r_status = #{status}, r_updated_at = NOW() " +
            "WHERE r_id = #{reservationId}")
    int updateReservationStatus(@Param("reservationId") Long reservationId, @Param("status") String status);

    @Update("UPDATE reservations SET r_status = '已取消', r_cancel_time = NOW(), r_cancel_reason = #{reason} " +
            "WHERE r_id = #{reservationId}")
    int cancelReservation(@Param("reservationId") Long reservationId, @Param("reason") String reason);

    @Select("SELECT COUNT(*) FROM reservations WHERE r_c_id = #{courseId} AND r_status IN ('预约中', '已确认')")
    int countActiveReservationsByCourse(@Param("courseId") Long courseId);

    @Select("SELECT r.r_id, u.u_nickname as user_name, c.c_name, co.name as coach_name, " +  // 改为 co.name
            "r.r_status, r.r_created_at, r.r_updated_at, r.r_notes " +
            "FROM reservations r " +
            "LEFT JOIN users u ON r.r_u_id = u.user_id " +
            "LEFT JOIN courses c ON r.r_c_id = c.c_id " +
            "LEFT JOIN coaches co ON r.r_t_id = co.id " +  // 确保表别名一致
            "WHERE r.r_status != '已取消' " +
            "ORDER BY r.r_created_at DESC")
    List<Map<String, Object>> findAllActiveReservations();

    @Select("SELECT r.r_id, u.u_nickname as user_name, c.c_name, co.name as coach_name, " +
            "r.r_status, r.r_created_at, r.r_cancel_time, r.r_cancel_reason, r.r_notes " +
            "FROM reservations r " +
            "LEFT JOIN users u ON r.r_u_id = u.user_id " +
            "LEFT JOIN courses c ON r.r_c_id = c.c_id " +
            "LEFT JOIN coaches co ON r.r_t_id = co.id " +
            "WHERE r.r_status = '已取消' " +
            "ORDER BY r.r_cancel_time DESC")
    List<Map<String, Object>> findAllCancelledReservations();
    
    // 统计活跃预约总数
    @Select("SELECT COUNT(*) FROM reservations WHERE r_status != '已取消'")
    Long countActiveReservations();
    
    // 统计待确认预约总数
    @Select("SELECT COUNT(*) FROM reservations WHERE r_status = '预约中'")
    Long countPendingReservations();
    
    // 批量确认所有待确认预约
    @Update("UPDATE reservations SET r_status = '已确认' WHERE r_status = '预约中'")
    Integer confirmAllPendingReservations();
    
    // 统计取消预约总数
    @Select("SELECT COUNT(*) FROM reservations WHERE r_status = '已取消'")
    Long countCancelledReservations();
    
    // 分页查询活跃预约
    @Select("SELECT r.r_id, u.u_nickname as user_name, c.c_name, co.name as coach_name, " +
            "r.r_status, r.r_created_at, r.r_updated_at, r.r_notes " +
            "FROM reservations r " +
            "LEFT JOIN users u ON r.r_u_id = u.user_id " +
            "LEFT JOIN courses c ON r.r_c_id = c.c_id " +
            "LEFT JOIN coaches co ON r.r_t_id = co.id " +
            "WHERE r.r_status != '已取消' " +
            "ORDER BY r.r_created_at DESC " +
            "LIMIT #{limit} OFFSET #{offset}")
    List<Map<String, Object>> findActiveReservationsByPage(@Param("limit") Integer limit, @Param("offset") Integer offset);
    
    // 分页查询取消预约
    @Select("SELECT r.r_id, u.u_nickname as user_name, c.c_name, co.name as coach_name, " +
            "r.r_status, r.r_created_at, r.r_cancel_time, r.r_cancel_reason, r.r_notes " +
            "FROM reservations r " +
            "LEFT JOIN users u ON r.r_u_id = u.user_id " +
            "LEFT JOIN courses c ON r.r_c_id = c.c_id " +
            "LEFT JOIN coaches co ON r.r_t_id = co.id " +
            "WHERE r.r_status = '已取消' " +
            "ORDER BY r.r_cancel_time DESC " +
            "LIMIT #{limit} OFFSET #{offset}")
    List<Map<String, Object>> findCancelledReservationsByPage(@Param("limit") Integer limit, @Param("offset") Integer offset);
}